package team.sun.sss.service.staff;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import team.sun.sss.dao.staff.*;
import team.sun.sss.dao.user.UserDao;
import team.sun.sss.pojo.staff.Staff;
import team.sun.sss.pojo.user.User;

import java.io.InputStream;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

@Service
public class StaffServiceImpl implements StaffService {
    @Autowired
    StaffDao staffDao;
    @Autowired
    UserDao userDao;

    @Override
    public List<Staff> getStaffList() {
        return staffDao.getStaffList();
    }

    @Override
    public int userStop(String userId) {
        return staffDao.userStop(userId);
    }

    @Override
    public int userStart(String userId) {
        return staffDao.userStart(userId);
    }

    @Override
    @Transactional(rollbackFor = {RuntimeException.class, Error.class})
    public int addToDatabase(MultipartFile file) throws Exception{
        int result = 0;
        List<Staff> StaffList = new ArrayList<>();                    // 创建billList

        String fileName = file.getOriginalFilename();                           // 获取上传的文件名
        String suffix = fileName.substring(fileName.lastIndexOf(".")+1);    // 获取上传文件后缀

        System.out.println("文件后缀为：" + suffix);
        InputStream inputStream = file.getInputStream();            // 输入流读取文件
        Workbook workbook = null;                                   // 1. 新建工作簿

        if(suffix.equals("xlsx")){
            workbook = new XSSFWorkbook(inputStream);               // Excel2007版本及以后 用XSSF(即Poi-ooxml)处理
        } else {
            workbook = new HSSFWorkbook(inputStream);               // Excel2003版本 用HSSF(即Poi)处理
        }

        Sheet sheet = workbook.getSheetAt(0);                     // 2. 获取当前工作表

        if(sheet != null){
            for (int i = 1; i <= sheet.getLastRowNum(); i++){       // 3. 从第二行开始遍历工作表的每一行
                Row row = sheet.getRow(i);
                if (row != null){
                    List<String> list = new ArrayList<>();
                    for (Cell cell : row){                          // 4. 遍历每一个单元格
                        if (cell != null) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);        // 将单元格值转化为字符串
                            String cellValue = cell.getStringCellValue();   // 获取字符串形式的值
                            list.add(cellValue);                            // 将值加入数组
                        }
                    }
                    int staNum = Integer.parseInt(list.get(0));
                    String staName = String.valueOf(list.get(1));
                    String staEmail = String.valueOf(list.get(2));
                    Boolean staSex = list.get(3)=="男"?true:false;
                    Boolean staNation = list.get(4)=="汉族"?true:false;
                    String staCollege = String.valueOf(list.get(5));
                    String staProvince = String.valueOf(list.get(6));
                    String staPoliticalOutlook = String.valueOf(list.get(7));
                    String staSort = String.valueOf(list.get(8));
                    String staEducationBackground = String.valueOf(list.get(9));
                    String staIdCard = String.valueOf(list.get(10));
                    String staDegree = String.valueOf(list.get(11));
                    Date staBirthday = Date.valueOf(list.get(12));
                    String staWorkType = String.valueOf(list.get(13));
                    Boolean staMarriage = list.get(14)=="已婚"?true:false;
                    Date staFirstWorkDate = Date.valueOf(list.get(15));
                    String staWorkTitle = String.valueOf(list.get(16));
                    String staPhoneNum = String.valueOf(list.get(17));
                    String staPostalCode = String.valueOf(list.get(18));
                    Boolean staWorkState = list.get(19)=="在职"?true:false;
                    int staCounsellorTime = Integer.parseInt(list.get(20));
                    String staWechat = String.valueOf(list.get(21));
                    int staQqNum = Integer.parseInt(list.get(22));
                    int userState = 0;
                    // 构造一个账单对象，并将从个单元格获取的值赋给它
                    Staff staff = new Staff(staNum, staName, staEmail, staSex, staNation,
                            staCollege, staProvince, staPoliticalOutlook, staSort, staEducationBackground, staIdCard,
                            staDegree,staBirthday,staWorkType,staMarriage,staFirstWorkDate,staWorkTitle,staPhoneNum,
                            staPostalCode,staWorkState,staCounsellorTime,staWechat,staQqNum,userState);
                    StaffList.add(staff);                                 // 将新的一条账单加入billList
                }
            }

            for (Staff staff: StaffList){
                result = staffDao.addStaff(staff);      // 将每一条账单插入数据库
                userDao.addUser(new User(staff.getStaNum(),"123",2,0)); //添加用户表
            }
        }
        return result;
    }
}
